<?php
namespace WDB\SQLDriver;
use WDB;
use WDB\Analyzer;
use WDB\Query\Element;
/**
 * MySQL driver implementation
 *
 * @author Richard Ejem <richard(at)ejem.cz>
 * @package WDB
 *
 * @property-read Analyzer\View[] $views
 * @property-read Analyzer\TableView[] $tablesViews
 */
final class MySQL extends BaseSQL implements iSQLDriver
{
    /** @var mysqli $c*/
    private $c;

    private $lastQueryString = '';

    public function changeSchema($schema)
    {
        if (!$this->c->select_db((string)$schema))
        {
            throw $this->analyzeLastError();
        }
    }
    public function getTablesViews(WDB\Analyzer\Schema $schema)
    {
        $table_list = $this->c->query($q="SELECT TABLE_NAME AS name, TABLE_TYPE AS type, TABLE_COMMENT AS comment FROM `information_schema`.`TABLES` WHERE TABLE_SCHEMA = ".$this->tosql_string($schema->name));
        $tables = array();
        while ($table = $table_list->fetch_assoc())
        {
            if ($table['type'] == 'BASE TABLE')
            {
                $tables[$table['name']] = new Analyzer\Table($table['name'], $table['comment'], $schema, $this);
            }
            elseif ($table['type'] == 'VIEW')
            {
                $tables[$table['name']] = new Analyzer\View($table['name'], $table['comment'], $schema, $this);
            }

        }
        return $tables;
    }

    public function getTableColumns(Analyzer\TableView $table)
    {
        $ts = $this->quote_string($this->escape_string($table->schema->getName()));
        $tn = $this->quote_string($this->escape_string($table->name));

        //foreign key constraints
        $fk = $this->getForeignForTable($table);
        //create foreignkey structure data
        $foreign = array();
        foreach ($fk as $name=>$key)
        {
            $cols = $refCols = array();
            foreach ($key as $row)
            {
                $cols[] = $row['column'];
                $refCols[] = $row['refColumn'];
            }
            $foreign[$name] = new WDB\Structure\ForeignKeyData(array(
                'name'=>$name,
                'schema'=>$table->schema->getName(),
                'table'=>$table->name,
                'columns'=>$cols,
                'refSchema'=>$key[0]['refSchema'],
                'refTable'=>$key[0]['refTable'],
                'refColumns'=>$refCols,
                'onUpdate'=>$this->fkeyAction($key[0]['onUpdate']),
                'onDelete'=>$this->fkeyAction($key[0]['onDelete']),
            ));
        }

        $info = $this->c->query("SELECT * FROM information_schema.columns WHERE table_schema=$ts AND table_name=$tn ORDER BY ordinal_position");
        $columns = array();
        //general column data
        while ($r = $info->fetch_assoc())
        {
            $colInfo = array(
                'default'=>$r['COLUMN_DEFAULT'],
                'nullable'=>($r['IS_NULLABLE'] == 'YES'),
                'charMaxLength'=>$r['CHARACTER_MAXIMUM_LENGTH'],
                'precision'=>$r['NUMERIC_PRECISION'],
                'scale'=>$r['NUMERIC_SCALE'],
                'extra'=>$r['EXTRA'],
                'comment'=>$r['COLUMN_COMMENT'],
                'signed'=>$this->signedType($r['COLUMN_TYPE']),
                'columnType'=>$r['COLUMN_TYPE'],
                'isAutoColumn'=>$r['EXTRA'] != NULL, //strongly driver-specific
                'isAutoIncrement'=>strtolower($r['EXTRA']) == 'auto_increment', //strongly driver-specific
            );
            if (isset($this->integer_ranges[$r['DATA_TYPE']]))
            {
                $min = $this->integer_ranges[$r['DATA_TYPE']][0];
                $max = $this->integer_ranges[$r['DATA_TYPE']][1];
                if ($colInfo['signed'])
                {
                    $max = bcadd($max, -$min);
                    $min = '0';
                }
                $colInfo['integerMin'] = $min;
                $colInfo['integerMax'] = $max;
            }
            $columns[$r['COLUMN_NAME']] = $this->columnForType($r['DATA_TYPE'], $r['COLUMN_NAME'], $table, new WDB\Structure\ColumnMetaInfo($colInfo));
        }
        //primary and unique key constraints
        $infokeys = $this->c->query("SHOW INDEX FROM ".$this->quote_identifier($table->schema->getName()).'.'.$this->quote_identifier($table->name));
        $primary = array();
        $unique = array();
        while ($r = $infokeys->fetch_assoc())
        {
            if (!$r['Non_unique'])
            {
                if (!isset($unique[$r['Key_name']]))
                {
                    $unique[$r['Key_name']] = array();
                }
                $unique[$r['Key_name']][] = $r['Column_name'];
            }
            if ($r['Key_name'] == 'PRIMARY')
            {
                $primary[] = $r['Column_name'];
            }
        }
        return new WDB\Structure\TableColumns(array('columns'=>$columns, 'unique'=>$unique, 'primary'=>$primary, 'foreign'=>$foreign));
    }

    public function schemaExists($schema)
    {
        $schema = $this->c->query(
                sprintf("SELECT SCHEMA_NAME FROM information_schema.schemata WHERE SCHEMA_NAME='%s'",
                        $this->c->escape_string($schema)));
        return ($schema->num_rows > 0);
    }

    public function queryRaw($query, $multi = FALSE)
    {
        if ($multi)
        {
            $result = $this->c->multi_query($query);
            while ($this->c->more_results()) {$this->c->next_result();}

        }
        else
        {
            $result = $this->driver_query($query);
            if (!$result)
            {
                throw $this->analyzeLastError();
            }
        }

        return new WDB\Query\RawResult(array('query'=>new WDB\Query\Raw($query), 'result'=>(bool)$result, 'nativeResult'=>$result));
    }

    public function select(WDB\Query\Select $select)
    {
        $q=$this->tosql_select($select);
        $result = $this->driver_query($q);
        if (!$result)
        {
            throw $this->analyzeLastError();
        }
        return new Result\MySQL_SelectResult($select, $result);
    }

    public function insert(WDB\Query\Insert $insert)
    {
        $q=$this->tosql_insert($insert);
        $result = $this->driver_query($q);
        if (!$result)
        {
            throw $this->analyzeLastError();
        }
        return new WDB\Query\WriteResult(array('affectedRows'=>$this->c->affected_rows, 'insertId'=>$this->c->insert_id, 'query'=>$insert, 'success'=>(bool)$result));
    }

    public function update(WDB\Query\Update $update)
    {
        if (count($update->getColumns()) == 0)
        {
            //nothing to write
            return new WDB\Query\WriteResult(array('affectedRows'=>0, 'insertId'=>NULL, 'query'=>$update, 'success'=>true));
        }
        $q=$this->tosql_update($update);
        $result = $this->driver_query($q);
        if (!$result)
        {
            throw $this->analyzeLastError();
        }
        return new WDB\Query\WriteResult(array('affectedRows'=>$this->c->affected_rows, 'insertId'=>NULL, 'query'=>$update, 'success'=>(bool)$result));
    }

    public function delete(WDB\Query\Delete $delete)
    {
        $q=$this->tosql_delete($delete);
        $result = $this->driver_query($q);
        if (!$result)
        {
            throw $this->analyzeLastError();
        }
        return new WDB\Query\WriteResult(array('affectedRows'=>$this->c->affected_rows, 'insertId'=>NULL, 'query'=>$delete, 'success'=>(bool)$result));
    }

    public function connect(WDB\Structure\ConnectionConfig $connection)
    {
        $port = ($connection->port === NULL) ? $connection->port : ini_get("mysqli.default_port");
        $this->c = @new \mysqli($connection->host,$connection->user,$connection->password,$connection->schema,$port);
        $e = mysqli_connect_errno();
        if ($e == 1049) throw new WDB\Exception\SchemaNotFound(mysqli_connect_error(), $e);
        elseif ($e != 0)
        {
            throw new WDB\Exception\ConnectionFailed(mysqli_connect_error(), $e);
        }
        if ($connection->charset !== NULL) $this->setCharset($connection->charset);
    }

    public function disconnect()
    {
        $this->c->close();
    }

    public function getCharset()
    {
        return $this->c->get_charset()->charset;
    }

    public function setCharset($charset)
    {
        $this->c->set_charset($charset);
    }

    protected function tosql_insert(WDB\Query\Insert $insert)
    {
        //mysql dialect update-like syntax INSERT INTO table SET col1=val1,col2=val2...
        //much better for human-readability and also machine building
        switch ($insert->mode)
        {
            case WDB\Query\Insert::IGNORE:
                $q = 'INSERT IGNORE INTO ';
                break;
            case WDB\Query\Insert::REPLACE:
                $q = 'REPLACE INTO ';
                break;
            case WDB\Query\Insert::UNIQUE:
            case WDB\Query\Insert::UPDATE:
            default:
                $q = 'INSERT INTO ';
                break;
        }
        $q .= $insert->table->toSQL($this);
        $q .= ' SET ';
        $q .= $this->tosql_assignList($insert->getDatabase() ? $insert->getDatabase()->getTable($insert->table) : NULL, $insert->columns);
        if ($insert->mode == WDB\Query\Insert::UPDATE)
        {
            $q .= ' ON DUPLICATE KEY UPDATE ';
            $q .= $this->tosql_assignList($insert->getDatabase() ? $insert->getDatabase()->getTable($insert->table) : NULL, $insert->ODKUcolumns);
        }
        return $q;
    }

    public function tosql_datetime(\DateTime $value = NULL, $date, $time)
    {
        if ($value === NULL) return $this->tosql_null();
        $datePattern = 'Y-m-d';
        $timePattern = 'H:i:s';
        if ($date && $time)
        {
            $d = $value->format("$datePattern $timePattern");
        }
        elseif ($date)
        {
            $d = $value->format($datePattern);
        }
        elseif ($time)
        {
            $d = $value->format($timePattern);
        }
        else
        {
            throw new WDBException("at least one of date or time parameters of iSQLDriver::tosql_datetime() must be TRUE");
        }
        return $this->quote_string($d);
    }
    public function tosql_time(\DateInterval $value = NULL)
    {
        if ($value === NULL) return $this->tosql_null();
        $timePattern = 'H:i:s';
        return $this->quote_string($value->format($timePattern));
    }

    private function signedType($type)
    {
        return preg_match('~\\bunsigned\\b~i', $type);
    }

    private $foreignKeyCache = NULL;

    /**
     * foreign key structure should be read by one query for whole schema because
     * this query is noticeably slow
     *
     * @param Analyzer\TableView $table
     * @return array[][][]
     */
    private function getForeignForTable($table)
    {
        $ts = $this->escape_string($table->schema->getName());
        $tn = $this->escape_string($table->name);
        if ($this->foreignKeyCache === NULL)
        {
            $foreign = $this->c->query("SELECT
           u.table_schema AS 'schema',
           u.table_name AS 'table',
           u.constraint_name AS 'name',
           u.column_name AS 'column',
           u.referenced_table_schema AS 'refSchema',
           u.referenced_table_name AS 'refTable',
           u.referenced_column_name AS 'refColumn',
           r.update_rule AS 'onUpdate',
           r.delete_rule AS 'onDelete'
          FROM information_schema.table_constraints AS c
          INNER JOIN information_schema.key_column_usage AS u
          USING( constraint_schema, constraint_name )
          INNER JOIN information_schema.referential_constraints AS r
          USING( constraint_schema, constraint_name )
          WHERE c.constraint_type = 'FOREIGN KEY'
          ORDER BY constraint_name");
            //transform key data to an associative array by schema and name with grouped fkeys over multiple columns to one element
            $this->foreignKeyCache = array();
            while ($row = $foreign->fetch_assoc())
            {
                if (!isset($this->foreignKeyCache[$row['schema']]))
                        $this->foreignKeyCache[$row['schema']] = array();
                if (!isset($this->foreignKeyCache[$row['schema']][$row['table']]))
                        $this->foreignKeyCache[$row['schema']][$row['table']] = array();
                if (!isset($this->foreignKeyCache[$row['schema']][$row['table']][$row['name']]))
                        $this->foreignKeyCache[$row['schema']][$row['table']][$row['name']] = array();
                $this->foreignKeyCache[$row['schema']][$row['table']][$row['name']][] = $row;
            }
        }

        if (isset($this->foreignKeyCache[$table->schema->getName()][$table->name]))
        {
            return $this->foreignKeyCache[$table->schema->getName()][$table->name];
        }
        else
        {
            return array();
        }
    }

    private function driver_query($q)
    {
        $this->lastQueryString = $q;
        return $this->c->query($q);
    }

    private function analyzeLastError()
    {
        //got from http://dev.mysql.com/doc/refman/5.0/en/error-messages-server.html
        switch ($this->c->errno)
        {
            case 1263:
            case 1048:
                return new WDB\Exception\QueryNotNullColumn($this->c->error, $this->lastQueryString, WDB\Utils\Strings::pregRead('~^column \'([^\']+)\'~', $this->c->error));
            case 1055:
                return new WDB\Exception\QueryWrongFieldWithGroup($this->c->error, $this->lastQueryString, WDB\Utils\Strings::pregRead('~\'([^\']+)\'$~', $this->c->error));
            case 1056:
                return new WDB\Exception\QueryWrongGroupField($this->c->error, $this->lastQueryString, WDB\Utils\Strings::pregRead('~^\'([^\']+)\'~', $this->c->error));
            case 1062:
                return new WDB\Exception\QueryDuplicateKeyEntry($this->c->error, $this->lastQueryString, WDB\Utils\Strings::pregRead('~\'([^\']+)\' for key ~', $this->c->error), WDB\Utils\Strings::pregRead('~\\\'?([^ ]+?)\\\'?$~', $this->c->error));
            case 1062:
            case 1149:
                return new WDB\Exception\QuerySyntaxError($this->c->error, $this->lastQueryString);
            case 1149:
                return new WDB\Exception\QueryUniqueViolation($this->c->error, $this->lastQueryString, WDB\Utils\Strings::pregRead('~\'([^\']+)\'$~', $this->c->error));
            case 1216:
            case 1451:
                return new WDB\Exception\QueryForeignChildViolation($this->c->error, $this->lastQueryString, WDB\Utils\Strings::pregRead('~\\(([^\\)]+)\\)$~', $this->c->error));
            case 1217:
            case 1452:
                return new WDB\Exception\QueryForeignParentViolation($this->c->error, $this->lastQueryString, WDB\Utils\Strings::pregRead('~\\(([^\\)]+)\\)$~', $this->c->error));
            case 1365:
                return new WDB\Exception\QueryDivisionByZero($this->c->error, $this->lastQueryString);
            case 1406:
                return new WDB\Exception\QueryDataTooLong($this->c->error, $this->lastQueryString, WDB\Utils\Strings::pregRead('~ for column \'([^\']+)\'~', $this->c->error), WDB\Utils\Strings::pregRead('~([0-9]+)$~', $this->c->error));
            default:
                return new WDB\Exception\QueryError($this->c->error, $this->lastQueryString);
        }

    }

    protected function quote_identifier($identifier)
    {
        return '`'.$this->escape_identifier($identifier).'`';
    }

    protected function quote_string($string)
    {
        return "'".$string."'";
    }

    /**
     * Escape string literal.
     *
     * @param string
     * @return string
     */
    protected function escape_string($string)
    {
        return $this->c->escape_string($string);
    }

    /**
     * Escape identifier.
     *
     * @param string
     * @return string
     */
    protected function escape_identifier($identifier)
    {
        if (strpos($identifier, "\0") !== FALSE)
        {
            throw new WDB\Exception\InvalidIdentifier("MySQL identifier cannot contain a null character");
        }
        $identifier = str_replace("`", '``', $identifier);
        return $identifier;
    }

    /**
     *
     * @param string $datatype
     * @param string $column_name
     * @param \WDB\Analyzer\TableView $table
     * @param \WDB\Structure\ColumnMetaInfo $meta
     * @return \WDB\Analyzer\Column
     */
    protected function columnForType($datatype, $column_name, \WDB\Analyzer\TableView $table, WDB\Structure\ColumnMetaInfo $meta)
    {
        $datatype = strtolower($datatype);
        switch ($datatype)
        {
            case 'char':
            case 'varchar':
            case 'binary':
            case 'varbinary':
            case 'tinytext':
            case 'text':
            case 'blob':
            case 'mediumtext':
            case 'mediumblob':
            case 'longtext':
            case 'longblob':
                return new \WDB\Analyzer\ColumnChar($column_name, $table, $meta);
            case 'tinyint':
            case 'smallint':
            case 'mediumint':
            case 'int':
            case 'bigint':
                return new \WDB\Analyzer\ColumnInteger($column_name, $table, $meta, $this->integer_ranges[$datatype]);
            case 'float':
            case 'double':
                return new \WDB\Analyzer\ColumnFloat($column_name, $table, $meta, $datatype == 'double');
            case 'decimal':
                return new \WDB\Analyzer\ColumnDecimal($column_name, $table, $meta);
            case 'date':
                return new \WDB\Analyzer\ColumnDate($column_name, $table, $meta);
            case 'datetime':
                return new \WDB\Analyzer\ColumnDateTime($column_name, $table, $meta);
            case 'time':
                return new \WDB\Analyzer\ColumnTime($column_name, $table, $meta);
            case 'timestamp':
                if (strtolower($meta->default) == 'current_timestamp') $meta->default = 'now';
                return new \WDB\Analyzer\ColumnTimestamp($column_name, $table, $meta, preg_match('~\\bon update current_timestamp\\b~i', $meta->extra));
            case 'enum':
                return new \WDB\Analyzer\ColumnEnum($column_name, $table, $meta, $this->parseSet($meta->columnType));
            case 'set':
                return new \WDB\Analyzer\ColumnSet($column_name, $table, $meta, $this->parseSet($meta->columnType));
            default:
                return new \WDB\Analyzer\Column($column_name, $table, $meta);
        }
    }

    public function startTransaction()
    {
        $this->queryRaw("START TRANSACTION");
    }
}